package com.gem.controller;

import com.alibaba.druid.util.StringUtils;
import com.gem.domain.*;
import com.gem.service.*;
import com.gem.util.ExcelUtil;
import com.gem.util.JSONResultUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.*;

/**
 * @author Apolar_GuanYH-管阳红
 * Description: 合作成果所有Excel导出控制器
 * create 2019/11/3 15:56
 * Modified By:
 */
@Controller
@SuppressWarnings("all")
@RequestMapping("/cr")
public class CCrAllExcelController {

    //自动注入JSONResultUtil
    private JSONResultUtil result;

    @Autowired
    public void setResult(JSONResultUtil result) {
        this.result = result;
    }

    //自动注入CCooperationResultService
    private CCooperationResultService cCoopreationResultService;

    @Autowired
    public void setcCoopreationResultService(CCooperationResultService cCoopreationResultService) {
        this.cCoopreationResultService = cCoopreationResultService;
    }

    //自动注入UserService
    private UserService userService;

    @Autowired
    public void setUserService(UserService userService) {
        this.userService = userService;
    }

    //自动注入TeacherService
    private TeacherService teacherService;

    @Autowired
    public void setTeacherService(TeacherService teacherService) {
        this.teacherService = teacherService;
    }

    //自动注入CompanyService
    private CompanyService companyService;

    @Autowired
    public void setCompanyService(CompanyService companyService) {
        this.companyService = companyService;
    }

    //自动注入CompanyService
    private CompanyContactService companyContactService;

    @Autowired
    public void setCompanyContactService(CompanyContactService companyContactService) {
        this.companyContactService = companyContactService;
    }

    //自动注入CCrCompanyService
    private CCrCompanyService cCrCompanyService;

    @Autowired
    public void setcCrCompanyService(CCrCompanyService cCrCompanyService) {
        this.cCrCompanyService = cCrCompanyService;
    }

    //自动注入CCrParticipantService
    private CCrParticipantService cCrParticipantService;

    @Autowired
    public void setcCrParticipantService(CCrParticipantService cCrParticipantService) {
        this.cCrParticipantService = cCrParticipantService;
    }

    //导出excel模板
    @RequestMapping("/exportTemplate")
    public void exportExcelTemplate(HttpServletResponse response, HttpSession session) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();//创建excel工作簿对象//一个表格文件！
            cooperationResultSheet(workbook);//合作成果sheet
            crCompanySheet(workbook);//参与公司sheet
//            crContactSheet(workbook);//参与人sheet
            OutputStream outputStream = response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=CooperationResultTemplate.xls");
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //导出excel数据
    @RequestMapping("/export")
    public void exportExcel(HttpServletResponse response, HttpSession session) {
        try {
            //创建excel工作簿对象//一个表格文件！
            HSSFWorkbook workbook = new HSSFWorkbook();
            crDataSheet(workbook);//创建合作成果数据sheet对象
            crCDataSheet(workbook);//参与公司sheet
            OutputStream outputStream = response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=CooperationResult.xls");
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //上传excel
    @RequestMapping(value = "/crUploadExcel", method = RequestMethod.POST)
    @ResponseBody
    public JSONResultUtil dsUploadExcel(@RequestParam(value = "file", required = false) MultipartFile file) {
        try {
            InputStream inputStream = null;
            inputStream = file.getInputStream();
            BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            List<CCooperationResult> list = getcCooperationResults(workbook);//获取合作成果数据
            List<CCrCompany> listCompany = getcCompanys(workbook);//获取参与公司数据
            if (list.size() > 0 && listCompany.size() > 0 && cCoopreationResultService.uploadExcelSheetsService(list, listCompany)) {
                result.setId(0);
                result.setData("上传成功！");
            } else {
                result.setId(1);
                result.setData("上传失败哦，请重试！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.setId(-1);
            result.setData("服务器异常，请联系管理员哦！");
        }
        return result;
    }

    //上传-获取合作成果数据
    private List<CCooperationResult> getcCooperationResults(HSSFWorkbook workbook) {
        HSSFSheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        CCooperationResult cCooperationResult = null;
        HSSFRow row = null;
        //读取数据
        List<CCooperationResult> list = new ArrayList<>();
        for (int i = 3; i <= lastRowNum; i++) {
            cCooperationResult = new CCooperationResult();
            row = sheet.getRow(i);
            if (row.getCell(1) == null
                    || (row.getCell(1).getDateCellValue()).equals("")
                    || StringUtils.isEmpty(row.getCell(2).getStringCellValue())
                    || StringUtils.isEmpty(row.getCell(3).getStringCellValue())
                    || StringUtils.isEmpty(row.getCell(7).getStringCellValue())
                    || StringUtils.isEmpty(row.getCell(10).getStringCellValue())) {
                break;//如果指定字段为空，停止写入
            }
            //读取数据加入对象
            String[] type = {"Integer", "Date", "String", "String", "String", "Double", "Double", "String", "Integer", "String", "String", "Integer", "String"};
            Object[] data = ExcelUtil.getExcelData(row, type);
            cCooperationResult.setCrDate((Date) data[1]);
            cCooperationResult.setCrType((String) data[2]);
            cCooperationResult.setCrTopic((String) data[3]);
            cCooperationResult.setCrTopicRequire((String) data[4]);
            cCooperationResult.setCrTopicUnitprice((Double) data[5]);
            cCooperationResult.setCrTopicTotalprice((Double) data[6]);
            Teacher teacher = teacherService.selectByName((String) data[7]);
            cCooperationResult.setCrCourseTid(teacher.getTId() + "");
            cCooperationResult.setCrCourseGrade((Integer) data[8] + "");
            cCooperationResult.setCrCourseEvaluation((String) data[9]);
            User user = userService.findByName((String) data[10]);
            cCooperationResult.setCrSalesUid(user.getUid() + "");
            cCooperationResult.setCrPersonNum((Integer) data[11]);
            cCooperationResult.setCrRemark((String) data[12]);
            list.add(cCooperationResult);
        }
        return list;
    }

    //上传-获取参与公司数据
    private List<CCrCompany> getcCompanys(HSSFWorkbook workbook) {
        HSSFSheet sheet = workbook.getSheetAt(1);
        int lastRowNum = sheet.getLastRowNum();
        CCrCompany cCrCompany = null;
        HSSFRow row = null;
        //读取数据
        List<CCrCompany> list = new ArrayList<>();
        for (int i = 3; i <= lastRowNum; i++) {
            cCrCompany = new CCrCompany();
            row = sheet.getRow(i);
            System.err.println("正在导入。。。");
            if (row.getCell(0) == null || StringUtils.isEmpty(row.getCell(1).getStringCellValue())) {
                break;//如果指定字段为空，停止写入
            }
            System.err.println("正在读取数据。。。");
            //读取数据加入对象
            String[] type = {"Integer", "String", "Double", "Double", "String"};
            Object[] data = ExcelUtil.getExcelData(row, type);
            cCrCompany.setCcCrId((Integer) data[0]);
            Company company = companyService.findByName((String) data[1]);
            cCrCompany.setCcCompanyId(company.getCoId());
            cCrCompany.setCcTopicUnitprice((Double) data[2]);
            cCrCompany.setCcTopicTotalprice((Double) data[3]);
            cCrCompany.setCcRemark((String) data[4]);
            list.add(cCrCompany);
        }
        return list;
    }

    //模板-合作成果sheet
    private void cooperationResultSheet(HSSFWorkbook workbook) {
        //创建sheet对象//一个表单！
        HSSFSheet sheet = workbook.createSheet("合作成果");

        //创建水平垂直居中的cellStyle单元格样式对象
        HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);

        //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
        HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);

        //模板第一行例子展示
        HSSFCellStyle cellStyle3 = ExcelUtil.getExamplStyle(workbook);

        // 单元格类型日期格式化
        HSSFCellStyle cellStyleDate = ExcelUtil.getDateRedStyle(workbook);

        //创建行对象
        HSSFRow row = null;

        // 将第一行的三个单元格给合并,模板固定
        //注意 java的参数是从0开始 即表示excel中的一行或者第一列  //Row行 Col列
        sheet = getRows(sheet, cellStyle, cellStyle2);
        //在第四行给一行数据模板,根据对应表的对应字段填写
        HSSFCell cell;
        row = sheet.createRow(3);
        Map<String, Object> map = new LinkedHashMap<>();
        map.put("Integer1", 0);
        map.put("Date", new Date());
        map.put("String1", "内训");
        map.put("String2", "java");
        map.put("String3", "合格");
        map.put("Double1", 17888.0);
        map.put("Double2", 20000.0);
        map.put("String4", "王老师");
        map.put("String5", "导入请格式化为数字!!");
        map.put("String6", "赞！");
        map.put("String7", "企培员工");
        map.put("Integer2", 50);
        map.put("String8", "备注信息");
        row = ExcelUtil.setExcelExample(row, map);
        row.getCell(0).setCellStyle(cellStyle3);
        row.getCell(1).setCellStyle(cellStyleDate);
        for (int i = 2; i < 13; i++) {
            row.getCell(i).setCellStyle(cellStyle3);
        }
    }

    //模板-参与公司sheet
    private void crCompanySheet(HSSFWorkbook workbook) {
        //创建sheet对象//一个表单！
        HSSFSheet sheet = workbook.createSheet("参与公司");

        //创建水平垂直居中的cellStyle单元格样式对象
        HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);

        //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
        HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);

        //模板第一行例子展示
        HSSFCellStyle cellStyle3 = ExcelUtil.getExamplStyle(workbook);

        //创建行对象
        HSSFRow row = null;

        // 将第一行的三个单元格给合并,模板固定
        //注意 java的参数是从0开始 即表示excel中的一行或者第一列  //Row行 Col列
        sheet = getCompanyRows(sheet, cellStyle, cellStyle2);
        //在第四行给一行数据模板,根据对应表的对应字段填写
        HSSFCell cell;
        row = sheet.createRow(3);
        Map<String, Object> map = new LinkedHashMap<>();
        map.put("Integer", 0);
        map.put("String1", "阿里巴巴");
        map.put("Double1", 17888.0);
        map.put("Double2", 20000.0);
        map.put("String2", "备注信息");
        row = ExcelUtil.setExcelExample(row, map);
        for (int i = 0; i < 5; i++) {
            row.getCell(i).setCellStyle(cellStyle3);
        }
    }

    //数据-合作成果sheet
    private void crDataSheet(HSSFWorkbook workbook) {
        HSSFSheet sheet = workbook.createSheet("合作成果");

        //创建水平垂直居中的cellStyle单元格样式对象
        HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);

        //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
        HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);

        //模板第一行例子展示
        HSSFCellStyle cellStyle3 = ExcelUtil.getExamplStyle(workbook);

        //数据
        HSSFCellStyle cellStyle4 = ExcelUtil.getCenterStyle(workbook);

        // 单元格类型日期格式化模板
        HSSFCellStyle cellStyleDate1 = ExcelUtil.getDateRedStyle(workbook);

        // 单元格类型日期格式化
        HSSFCellStyle cellStyleDate2 = ExcelUtil.getDateStyle(workbook);

        //创建行对象
        HSSFRow row = null;

        // 将第一行的三个单元格给合并,模板固定
        //注意 java的参数是从0开始 即表示excel中的一行或者第一列  //Row行 Col列
        sheet = getRows(sheet, cellStyle, cellStyle2);
        //在第四行给一行数据模板,根据对应表的对应字段填写
        HSSFCell cell;
        List<CCooperationResult> cCooperationResultList = cCoopreationResultService.selectAllCR();
        CCooperationResult cCooperationResult;
        for (int i = 0; i < cCooperationResultList.size(); i++) {
            cCooperationResult = cCooperationResultList.get(i);
            row = sheet.createRow(3 + i);
            Map<String, Object> map = new LinkedHashMap<>();
            map.put("Integer1", cCooperationResult.getCrId());
            map.put("Date", cCooperationResult.getCrDate());
            map.put("String1", cCooperationResult.getCrType());
            map.put("String2", cCooperationResult.getCrTopic());
            map.put("String3", cCooperationResult.getCrTopicRequire());
            map.put("Double1", cCooperationResult.getCrTopicUnitprice());
            map.put("Double2", cCooperationResult.getCrTopicTotalprice());
            map.put("String4", cCooperationResult.getCrCourseTeacher());
            map.put("String5", cCooperationResult.getCrCourseGrade());
            map.put("String6", cCooperationResult.getCrCourseEvaluation());
            map.put("String7", cCooperationResult.getCrSales());
            map.put("Integer2", cCooperationResult.getCrPersonNum());
            map.put("String8", cCooperationResult.getCrRemark());
            row = ExcelUtil.setExcelExample(row, map);
            row.getCell(0).setCellStyle(cellStyle4);
            row.getCell(1).setCellStyle(cellStyleDate2);
            for (int j = 2; j < 13; j++) {
                row.getCell(j).setCellStyle(cellStyle4);
            }
        }
    }

    //数据-参与公司sheet
    private void crCDataSheet(HSSFWorkbook workbook) {
        HSSFSheet sheet = workbook.createSheet("参与公司");

        //创建水平垂直居中的cellStyle单元格样式对象
        HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);

        //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
        HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);

        //模板第一行例子展示
        HSSFCellStyle cellStyle3 = ExcelUtil.getExamplStyle(workbook);

        //数据
        HSSFCellStyle cellStyle4 = ExcelUtil.getCenterStyle(workbook);

        //创建行对象
        HSSFRow row = null;

        // 将第一行的三个单元格给合并,模板固定
        //注意 java的参数是从0开始 即表示excel中的一行或者第一列  //Row行 Col列
        sheet = setCompanyRows(sheet, cellStyle, cellStyle2);
        //在第四行给一行数据模板,根据对应表的对应字段填写
        HSSFCell cell;
        CCrCompany cCrCompany;
        List<CCooperationResult> cCooperationResultList = cCoopreationResultService.selectAllCR();
        int n = 0;
        for (CCooperationResult cCooperationResult : cCooperationResultList) {
            List<CCrCompany> cCrCompanyList = cCrCompanyService.selectCCByccCrIdService(cCooperationResult.getCrId());
            for (int i = 0; i < cCrCompanyList.size(); i++, n++) {
                cCrCompany = cCrCompanyList.get(i);
                row = sheet.createRow(3 + n);
                Map<String, Object> map = new LinkedHashMap<>();
                map.put("Integer1", cCooperationResult.getCrId());
                map.put("String1", cCrCompany.getCcCompanyName());
                List<CompanyContact> companyContactList = cCrParticipantService.selectConByccIdService(cCrCompany.getCcId());
                map.put("Integer2", companyContactList.size());
                map.put("Double1", cCrCompany.getCcTopicUnitprice());
                map.put("Double2", cCrCompany.getCcTopicTotalprice());
                map.put("String3", cCrCompany.getCcRemark());
                row = ExcelUtil.setExcelExample(row, map);
                for (int j = 0; j < 6; j++) {
                    row.getCell(j).setCellStyle(cellStyle4);
                }
            }
        }

    }

    //格式-合作成果导出模板_表
    private HSSFSheet getRows(HSSFSheet sheet, HSSFCellStyle cellStyle, HSSFCellStyle cellStyle2) {
        HSSFRow row;// 将第一行的三个单元格给合并,模板固定
        //注意 java的参数是从0开始 即表示excel中的一行或者第一列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));
        row = sheet.createRow(0);
        HSSFCell beginCell0 = row.createCell(0);
        beginCell0.setCellValue("合作成果表");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell0.setCellStyle(cellStyle);

        //第二行的单元格进行合并,语句提示
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 12));
        row = sheet.createRow(1);
        HSSFCell beginCell1 = row.createCell(0);
        beginCell1.setCellValue("请删除样例后按照模板字段和单元格格式提交数据（编号不用填！）");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell1.setCellStyle(cellStyle);

        HSSFRow headRow = sheet.createRow(2);
        String[] hander = {"编号", "合作日期", "成果类型", "成果主题", "主题要求", "预期主题单价", "预期主题总价", "授课讲师", "课程评星", "课程评价", "销售专员", "预期参与人数", "备注"};
        headRow = ExcelUtil.setExcelHander(headRow, hander);
        headRow.setHeightInPoints(25); //设置行的高度
        for (int i = 0; i < 13; i++) {
            headRow.getCell(i).setCellStyle(cellStyle2);
        }
        //下拉列表的参数
        //编号禁止填写
        String[] idArr = {};
        //成果类型
        String[] typeArr = {"内训"};
        //授课讲师
        List<Teacher> teacherList = teacherService.selectAllTeacher();
        String[] teacherArr = new String[teacherList.size()];
        int indexT = 0;
        for (Teacher teacher : teacherList) {
            teacherArr[indexT] = teacher.getTName();
            indexT++;
            if(indexT==255){
                break;
            }
        }
        //星级
        String[] courseGradeArr = {"0", "1", "2", "3", "4", "5"};
        //销售专员
        List<User> userList = userService.findEmembers();
        String[] usersArr = new String[userList.size()];
        int index = 0;
        for (User user : userList) {
            usersArr[index] = user.getPersonname();
            index++;
            if(indexT==255){
                break;
            }
        }
        //设置当前工作表的什么地方需要下拉,需要自己对着表格设置位置,没有就不需要写
        sheet = ExcelUtil.setHSSFValidation(sheet, idArr, 3, 10000, 0, 0);
        sheet = ExcelUtil.setHSSFValidation(sheet, typeArr, 3, 10000, 2, 2);
//        sheet = ExcelUtil.setHSSFValidation(sheet, teacherArr, 3, 10000, 7, 7);
        sheet = ExcelUtil.setHSSFValidation(sheet, courseGradeArr, 3, 10000, 8, 8);
        sheet = ExcelUtil.setHSSFValidation(sheet, usersArr, 3, 10000, 10, 10);

        //设置所有列列宽
        for (int i = 0; i < 13; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        } // 设置列的宽度
        return sheet;
    }

    //格式-参与公司导入模板_表
    private HSSFSheet getCompanyRows(HSSFSheet sheet, HSSFCellStyle cellStyle, HSSFCellStyle cellStyle2) {
        HSSFRow row;// 将第一行的三个单元格给合并,模板固定
        //注意 java的参数是从0开始 即表示excel中的一行或者第一列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
        row = sheet.createRow(0);
        HSSFCell beginCell0 = row.createCell(0);
        beginCell0.setCellValue("参与公司表(请先上传成果表再单独提交本表)");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell0.setCellStyle(cellStyle);

        //第二行的单元格进行合并,语句提示
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
        row = sheet.createRow(1);
        HSSFCell beginCell1 = row.createCell(0);
        beginCell1.setCellValue("删除样例后按格式提交（编号来自先最新提交的50条成果表编号）");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell1.setCellStyle(cellStyle);

        HSSFRow headRow = sheet.createRow(2);
        String[] hander = {"合作成果编号", "企业名", "主题单价", "主题总价", "备注"};
        headRow = ExcelUtil.setExcelHander(headRow, hander);
        headRow.setHeightInPoints(25); //设置行的高度
        for (int i = 0; i < 5; i++) {
            headRow.getCell(i).setCellStyle(cellStyle2);
        }
        //下拉列表的参数
        //合作成果编号参数
        List<CCooperationResult> cCooperationResultList = cCoopreationResultService.selectAllCR();
        String[] crIdArr = new String[cCooperationResultList.size()];
        int index = 0;
        for (CCooperationResult cCooperationResult : cCooperationResultList) {
            crIdArr[index] = cCooperationResult.getCrId() + "";
            index++;
        }
        String[] crIdTemp = new String[50];
        for (int i = cCooperationResultList.size()-1; i > cCooperationResultList.size() - 51; i--) {
            crIdTemp[cCooperationResultList.size()-i-1] = crIdArr[i];
        }
        //企业名
        List<Company> companyList = companyService.findAllCompany();
        String[] companyArr = new String[companyList.size()];
        int indexT = 0;
        for (Company company : companyList) {
            companyArr[indexT] = company.getCoName();
            indexT++;
        }

        //设置当前工作表的什么地方需要下拉,需要自己对着表格设置位置,没有就不需要写
        sheet = ExcelUtil.setHSSFValidation(sheet, crIdTemp, 3, 10000, 0, 0);
        sheet = ExcelUtil.setHSSFValidation(sheet, companyArr, 3, 10000, 1, 1);

        //设置所有列列宽
        for (int i = 0; i < 5; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        } // 设置列的宽度
        return sheet;
    }

    //格式-参与公司导出数据_表
    private HSSFSheet setCompanyRows(HSSFSheet sheet, HSSFCellStyle cellStyle, HSSFCellStyle cellStyle2) {
        HSSFRow row;// 将第一行的三个单元格给合并,模板固定
        //注意 java的参数是从0开始 即表示excel中的一行或者第一列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
        row = sheet.createRow(0);
        HSSFCell beginCell0 = row.createCell(0);
        beginCell0.setCellValue("参与公司表");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell0.setCellStyle(cellStyle);

        //第二行的单元格进行合并,语句提示
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));
        row = sheet.createRow(1);
        HSSFCell beginCell1 = row.createCell(0);
        beginCell1.setCellValue("请删除样例后按照模板格式提交数据");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell1.setCellStyle(cellStyle);

        HSSFRow headRow = sheet.createRow(2);
        String[] hander = {"合作成果编号", "企业名", "参与总人数", "主题单价", "主题总价", "备注"};
        headRow = ExcelUtil.setExcelHander(headRow, hander);
        headRow.setHeightInPoints(25); //设置行的高度
        for (int i = 0; i < 6; i++) {
            headRow.getCell(i).setCellStyle(cellStyle2);
        }
        //下拉列表的参数
        //合作成果编号参数
        List<CCooperationResult> cCooperationResultList = cCoopreationResultService.selectAllCR();
        String[] crIdArr = new String[cCooperationResultList.size()];
        int index = 0;
        for (CCooperationResult cCooperationResult : cCooperationResultList) {
            crIdArr[index] = cCooperationResult.getCrId() + "";
            index++;
        }
        String[] crIdTemp = new String[50];
        for (int i = cCooperationResultList.size()-1; i > cCooperationResultList.size() - 51; i--) {
            crIdTemp[cCooperationResultList.size()-i-1] = crIdArr[i];
        }
        //企业名
        List<Company> companyList = companyService.findAllCompany();
        String[] companyArr = new String[companyList.size()];
        int indexT = 0;
        for (Company company : companyList) {
            companyArr[indexT] = company.getCoName();
            indexT++;
        }

        //设置当前工作表的什么地方需要下拉,需要自己对着表格设置位置,没有就不需要写
        sheet = ExcelUtil.setHSSFValidation(sheet, crIdTemp, 3, 10000, 0, 0);
        sheet = ExcelUtil.setHSSFValidation(sheet, companyArr, 3, 10000, 1, 1);

        //设置所有列列宽
        for (int i = 0; i < 6; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        } // 设置列的宽度
        return sheet;
    }
}
